import math

from sqlalchemy import create_engine, MetaData, Table, text, column
from sqlalchemy.orm import sessionmaker

#
engine = create_engine('mysql+pymysql://root:iagnjG3SfC160DQA@47.105.55.191:3306/population?charset=utf8mb4')
metadata = MetaData()

# 建立会话
Session = sessionmaker(bind=engine)
session = Session()


class Geography():
    Dic = {}

    # 获取年份和分类
    def year_divide(self, table_name):
        # 反射特定表
        table = Table(table_name, metadata, autoload_with=engine)

        # 获取年份
        column_names = [column.name for column in table.columns]
        year = column_names[1:]
        column_name = column_names[0]

        # 获取分类

        divide = session.query(table.c[column_name]).all()
        divide1 = []
        for i in divide:
            divide1.append(i[0])

        return year, divide1, column_name, table

    # 获取一行
    def item_fetch(self, table, divide_name, divide):
        with engine.connect() as connection:
            #   按省份获取一整行数据
            query = table.select().where(table.c[divide_name] == divide)
            result = connection.execute(query)
            item = result.fetchone()  # 一行数据

        return item

    # 获取一列
    def column_fetch(self, table, column_name):
        re = []
        for i in session.query(table.c[column_name]).all():
            re.append(i[0])
        return re  # 一列

    # 查询整张表
    def table_fetch(self, table_name):
        # 反射表结构
        metadata = MetaData()
        table = Table(table_name, metadata, autoload_with=engine)

        # 查询整张表
        query = table.select()
        result_proxy = session.execute(query)

        # 获取所有结果
        results = result_proxy.fetchall()

        return results

    # 按年查询数据

    def staticdata(self, table_name):
        dic = {}

        # 获取年份和分类
        year, divide, column_name, table = self.year_divide(table_name)

        # result = session.execute(Table(table_name, metadata, autoload_with=engine).select())
        #
        # results = result.fetchall()
        # for row in results:
        #     print(row)

        for i in year:
            # item = session.query(table.c[i]).all()      #一列
            item = self.column_fetch(table, i)  # 一列
            count = 0
            li = []  # 存储每个省数据（字典）的列表
            # print(item)
            for j in divide:
                value = item[count]
                if (value == None):
                    value = 0
                li.append({j: float(value)})  # 加入一年各个数据的字典
                count += 1
            dic[int(i[:-1])] = li
            # print(li)
        return dic

    # 按省查询

    def staticprovince(self, table_name):
        dic = {}

        # # 反射特定表
        # table = Table(table_name, metadata, autoload_with=engine)

        # 获取年份和分类
        year, divide, column_name, table = self.year_divide(table_name)

        table_data = self.table_fetch(table_name)

        count_divide = 0
        for i in divide:
            # print(i)
            # item = session.query(i)
            # item = self.item_fetch(table, column_name, i)  # 一行数据
            # print(item)
            count = 1
            li = []  # 存储每个省数据（字典）的列表

            for j in year:
                value = table_data[count_divide][count]
                if value == None:
                    value = 0
                li.append({j: float(value)})  # 加入一年各个数据的字典
                count += 1
            dic[i] = li
        return dic


    def country(self):
        country = "province_village_population"
        self.Dic["country"] = self.staticprovince(country)

    def urban(self):
        urban = "province_town_population"
        self.Dic["urban"] = self.staticprovince(urban)

    def resident(self):
        resident = "province_permanent_resident_population"
        self.Dic["resident"] = self.staticprovince(resident)

    def resident_year(self):
        resident = "province_permanent_resident_population"
        self.Dic["resident_year"] = self.staticdata(resident)


    def migration(self):
        migration = "migration_population_data"
        self.Dic["migration"] = self.staticprovince(migration)

    def migration_new(self):
        migration = "migration_population_data"
        self.Dic["migration_new"] = self.staticdata(migration)

    def density(self):
        density = "province_population_density"
        self.Dic["density"] = self.staticprovince(density)

    def density_new(self):
        density = "province_population_density"
        self.Dic["density_new"] = self.staticdata(density)

    def urbanization(self):
        urbanization = "province_town_rate"
        self.Dic["urbanization"] = self.staticprovince(urbanization)


    def urbanization_new(self):
        urbanization = "province_town_rate"
        self.Dic["urbanization_new"] = self.staticdata(urbanization)

    def getdata(self):
        self.country()
        self.urban()
        self.resident()
        self.migration()
        self.density()
        self.urbanization()
        self.density_new()
        self.migration_new()
        self.urbanization_new()
        self.resident_year()

g = Geography()
g.getdata()